﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.OleDb;

namespace EC_DataBase
{
    //   数据库操作类，封装了执行SQL语句的过程
    public static class AccessDB
    {

        //Provider=Microsoft.Jet.OLEDB.4.0;Data   Source=D:\JKD.MDB;Jet   OLEDB:Database   Password=123
        //private   static   string   con   =   @ "provider=microsoft.jet.oledb.4.0;data   source=D:\ProjectDemo\ArticleSystem\ArticleSystem\DataBase\ArticleSysDB.mdb;user   id=Admin;password=Abc123 ";//   =   System.Configuration.ConfigurationManager.AppSettings[ "DataConStr "].ToString();
        private static readonly string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath(@"\App_Data\EasyCheque.accdb");
        //private static readonly string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='../EasyCheque\EasyCheque\App_Data\EasyCheque.accdb';Persist Security Info=True";
        //private static readonly string con =@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath("EasyCheque.accdb");
        private static System.Data.OleDb.OleDbConnection dbCon;
        private static System.Data.OleDb.OleDbDataAdapter dbAdapter;
        private static System.Data.OleDb.OleDbCommand dbCommand;
        private static DataTable dt;

        private const char TYPE_DATE = 'D';
        private const char TYPE_TEXT = 'T';
        private const char TYPE_NUM = 'N';
        //public     DBCommon()
        //{
        //         //   DBCommon类在实例化时打开数据库连接！
        //         ConOpen();
        //}
        //打开数据库连接
        private static void ConOpen()
        {
            if (dbCon == null)
            {
                dbCon = new System.Data.OleDb.OleDbConnection(con);
            }
            if (dbCon.State != System.Data.ConnectionState.Open)
            {
                try
                {
                    dbCon = new System.Data.OleDb.OleDbConnection(con);
                    dbCon.Open();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
            }
        }

        //   关闭数据库连接
        public static void ConClose()
        {
            if (dbCon != null)
            {
                dbCon.Close();
                dbCon.Dispose();
            }
        }

        #region     Old   Code
        //   执行带有存储过程参数的sql语句，并返回DataTable，该方法采用了参数形式的SQL语句，
        //   即：参数化SQL语句，而给组合式的SQL语句，可以防止SQL注入等漏洞
        public static DataTable ExectueTableWithParams(string sql, System.Data.OleDb.OleDbParameter[] param)
        {
            ConOpen();
            dbAdapter = new System.Data.OleDb.OleDbDataAdapter(sql, dbCon);
            dbAdapter.SelectCommand.CommandText = sql;
            for (int i = 0; i < param.Length; i++)
            {
                dbAdapter.SelectCommand.Parameters.Add(param[i]);
            }
            string x = dbAdapter.SelectCommand.CommandText;
            DataTable dt = new DataTable();
            dbAdapter.Fill(dt);
            int j = dt.Rows.Count;
            sql = " ";
            ConClose();
            return dt;
        }

        //执行执行带有存储过程参数的sql语句，影响记录条数大与1时返回True，否则为False，参数化SQL语句，好处如上注释
        public static bool ExectueSqlWithParams(string sql, System.Data.OleDb.OleDbParameter[] param)
        {
            ConOpen();
            dbCommand = new System.Data.OleDb.OleDbCommand(sql, dbCon);
            dbCommand.CommandType = System.Data.CommandType.Text;

            int i = 0;
            for (i = 0; i < param.Length; i++)
            {
                dbCommand.Parameters.Add(param[i]);
            }
            i = dbCommand.ExecuteNonQuery();
            dbCon.Close();
            ConClose();
            sql = " ";
            if (i > 0)
                return true;
            else
                return false;
        }

        //   执行纯SQL语句，返回DataTable
        public static DataTable ExectueSql(string sql)
        {
            System.Diagnostics.Debug.WriteLine("SQL: " + sql);
            ConOpen();
            dbAdapter = new System.Data.OleDb.OleDbDataAdapter(sql, dbCon);
            dt = new DataTable();
            dbAdapter.Fill(dt);
            ConClose();
            sql = " ";
            return dt;
        }

        //   执行纯SQL语句，返回Bool
        public static bool ExectueSqlForBool(string sql, ref string INC_ID, string TABLE_NAME)
        {
            ConOpen();
            dbCommand = new System.Data.OleDb.OleDbCommand(sql, dbCon);
            INC_ID = "";
            if (TABLE_NAME != "" && TABLE_NAME!="ACCOUNT_INFO")
            {
                OleDbDataAdapter ODDA = new OleDbDataAdapter(@"SELECT @@IDENTITY FROM " + TABLE_NAME, dbCon);
                DataSet DS = new DataSet();
                ODDA.Fill(DS);
                if (DS.Tables[0].Rows.Count != 1)
                    INC_ID = "";
                else
                    INC_ID = DS.Tables[0].Rows[0][0].ToString();
            }
            int i = dbCommand.ExecuteNonQuery();
            ConClose();
            sql = " ";
            if (i > 0)
                return true;
            else
                return false;
        }
        public static bool ExectueSqlForBool(string sql)
        {
            string temp = "";
            return ExectueSqlForBool(sql, ref temp, "");
        }
        //执行纯SQL语句，返回影响的记录条数  
        public static int ExectueSqlReturnCount(string sql)
        {
            ConOpen();
            dbCommand = new System.Data.OleDb.OleDbCommand(sql, dbCon);
            int i = dbCommand.ExecuteNonQuery();
            sql = " ";
            ConClose();
            return i;
        }

        //   执行纯SQL语句，返回结果集的第一行，第一列的值
        public static object ExectueSqlReturnScalar(string sql)
        {
            ConOpen();
            dbCommand = new System.Data.OleDb.OleDbCommand(sql, dbCon);
            object str = dbCommand.ExecuteScalar().ToString();
            ConClose(); sql = " ";
            return str;
        }

        public static System.Data.OleDb.OleDbDataReader ExectueReader(string sql)
        {
            ConOpen();
            System.Data.OleDb.OleDbDataReader r;
            dbCommand = new System.Data.OleDb.OleDbCommand(sql, dbCon);
            sql = " ";
            r = dbCommand.ExecuteReader();
            return r;
            //     ConClose();

        }

        public static bool ExectueByTran(string sql)
        {
            sql = " ";
            //ConOpen();
            //dbCon.BeginTransaction();
            return false;

        }
        #endregion

        #region WADE_DB
        #region Select
        /*Select
             * 输入：要查找的列名[]，查找需要的表[]，条件
             * 输出：查得的数据(DataTable)
             */
        public static DataTable Select(string[] str_arrColumns, string[] str_arrTables, string str_Condition)
        {
            //拼接Select 子句
            string str_Sql = "select ";
            for (int i = 0; i < str_arrColumns.Length; i++)
            {
                str_Sql += str_arrColumns[i];
                if (i != str_arrColumns.Length - 1)
                {
                    str_Sql += ",";
                }
            }
            //拼接from子句
            str_Sql += " from ";
            for (int i = 0; i < str_arrTables.Length; i++)
            {
                str_Sql += str_arrTables[i];
                if (i != str_arrTables.Length - 1)
                {
                    str_Sql += ",";
                }
            }

            //拼接where 子句
            str_Sql += " where " + str_Condition;
            return ExectueSql(str_Sql);
        }
        #endregion
        #region Insert
        /*
      * Insert:向数据库中指定表中插入一项
      *输入：（表名，各列值[]）
      *输出：是否插入成功
      */
        public static bool Insert(string str_TableName, string[] str_arrayValues)
        {
            string Temp = "";
            return Insert(str_TableName, str_arrayValues, ref Temp);
        }
        public static bool Insert(string str_TableName, string[] str_arrayValues, ref string INC_ID)
        {
            string str_CombinedValues = "";
            char[] arrSign = new char[20];//标记数据库中表的各列类型
            for (int i = 0; i < 20; i++)
            {
                arrSign[i] = TYPE_TEXT;
            }
            if (str_TableName == "PERSON_INFO")
            {
                arrSign[3] = TYPE_DATE;
                arrSign[10] = TYPE_DATE;
                arrSign[12] = TYPE_NUM;
                str_CombinedValues = CombineValues(str_arrayValues, arrSign);
            }
            else if (str_TableName == "PERSON_ACCOUNT")
            {
                arrSign[0] = TYPE_NUM;
                str_CombinedValues = CombineValues(str_arrayValues, arrSign);
            }
            else if (str_TableName == "ACCOUNT_INFO")
            {
                arrSign[2] = TYPE_DATE;
                arrSign[4] = TYPE_NUM;
                arrSign[5] = TYPE_NUM;
                arrSign[6] = TYPE_NUM;
                str_CombinedValues = CombineValues(str_arrayValues, arrSign);
            }
            else if (str_TableName == "TRANS_DETAIL")
            {
                arrSign[0] = TYPE_NUM;
                arrSign[2] = TYPE_NUM;
                arrSign[5] = TYPE_DATE;
                arrSign[7] = TYPE_NUM;
                str_CombinedValues = CombineValues(str_arrayValues, arrSign);
            }
            else if (str_TableName == "CERTIFICATES")
            {
                arrSign[1] = TYPE_DATE;
                arrSign[2] = TYPE_NUM;
                arrSign[3] = TYPE_NUM;
                str_CombinedValues = CombineValues(str_arrayValues, arrSign);
            }
            else if (str_TableName == "CHEQUE_BOOK")
            {
                arrSign[0] = TYPE_NUM;
                arrSign[1] = TYPE_NUM;
                arrSign[2] = TYPE_NUM;
                arrSign[3] = TYPE_DATE;
                arrSign[4] = TYPE_NUM;
                str_CombinedValues = CombineValues(str_arrayValues, arrSign);
            }
            else if (str_TableName == "CHEQUE_INFO")
            {
                arrSign[0] = TYPE_NUM;
                arrSign[2] = TYPE_DATE;
                arrSign[3] = TYPE_NUM;
                arrSign[4] = TYPE_NUM;
                arrSign[7] = TYPE_NUM;
                arrSign[8] = TYPE_DATE;
                str_CombinedValues = CombineValues(str_arrayValues, arrSign);
            }

            return Insert_into(str_TableName, str_CombinedValues, ref INC_ID);
        }

        //将所给的各列值数组转换为SQL values（……）中的语句。
        private static string CombineValues(string[] str_arrValues, char[] arrSign)
        {
            string str_Conbined_Values = "";
            for (int i = 0; i < str_arrValues.Length; i++)
            {
                switch (arrSign[i])
                {
                    case TYPE_TEXT:
                        //加上单引号
                        str_Conbined_Values = str_Conbined_Values + "'" + str_arrValues[i] + "'";
                        break;
                    case TYPE_NUM:
                        str_Conbined_Values = str_Conbined_Values + str_arrValues[i];
                        break;
                    case TYPE_DATE:
                        //加上##
                        str_Conbined_Values = str_Conbined_Values + "#" + str_arrValues[i] + "#";
                        break;
                }
                if (i != str_arrValues.Length - 1)
                {
                    //加逗号
                    str_Conbined_Values += ",";
                }
            }
            return str_Conbined_Values;
        }
        /*
* Insert_into:向数据库中指定表中插入一项
*输入：（表名，各列值）
*输出：是否插入成功
*/
        private static bool Insert_into(string str_TableName, string str_Conbined_Values, ref string INC_ID)
        {
            try
            {

                string str_Sql = "insert into " + str_TableName;
                if (str_TableName == "PERSON_INFO")
                {
                    str_Sql += "(C_NAME,SEX,ENAME,BIRTHDAY,IDCARD,ADDRESS,PHONE,EMAIL,[PASSWORD],ACCOUNT,REGTIME,CELLPHONE,STATE, SECURITYCODE) ";
                }
                else if (str_TableName == "CERTIFICATES")
                {
                    str_Sql += "(PUB_KEY,REG_TIME,PID,STATE)";
                }
                else if (str_TableName == "ACCOUNT_INFO")
                {
                    str_Sql += "(ACCOUNT_ID, BANK, REGTIME, ACC_TYPE, [PERSON], USEABLE, [MONEY], PAYPASSWORD)";
                }

                str_Sql += " values(" + str_Conbined_Values + ")";

                System.Diagnostics.Debug.WriteLine("INSERT SQL: " + str_Sql);
                return ExectueSqlForBool(str_Sql, ref INC_ID, str_TableName);
            }
            catch
            {
                return false;
            }
        }
        private static bool Insert_into(string str_TableName, string str_Conbined_Values)
        {
            string Temp = "";
            return Insert_into(str_TableName, str_Conbined_Values, ref Temp);
        }
        #endregion
        #region Update

        /*Update:更新
                * 输入：（表名，要改的值，条件）
                * 输出：更改的记录数
                * 
                */
        public static int Update(string str_TableName, string str_Set_Condition, string str_Where_Condition)
        {
            try
            {
                string str_Sql = "update " + str_TableName + " set " + str_Set_Condition + " where " + str_Where_Condition;
                System.Diagnostics.Debug.WriteLine("The SQL in Update: " + str_Sql);
                return ExectueSqlReturnCount(str_Sql);
            }
            catch
            {
                return 0;
            }
        }
        #endregion
        #region Delete

        /*
                 * Delete：删除数据库指定表中的某几条满足条件的数据。
                 *输入：（表名，条件） 
                 * 返回值：删除的条数。
                 */
        public static int Delete(string str_TableName, string str_Condition)
        {
            try
            {
                string str_Sql = "delete from " + str_TableName + " where " + str_Condition;
                return ExectueSqlReturnCount(str_Sql);
            }
            catch
            {
                return 0;
            }
        }

        #endregion
        #endregion
    }
}
